SELECT
	gs.OPER_SEAT,
	g.FACT_SN,
	gs.FLOW,
	gs.IMSI,
	g.fact_id,
	g.ORG_ID,
	gs. STATUS,
	gs.PAY_STATUS,
	gs.TEL_NUM,
	gs.TEL_NUM_ENCRYPT,
	(SELECT
			ORG_NM
		FROM
			UAP.T_UAP_ORG j     
		WHERE
			j.PARENT_ORG IS NULL START WITH j.ORG_UUID = g.ORG_ID CONNECT BY PRIOR j.PARENT_ORG = j.ORG_UUID) 
topAgentOrgNm,(
		SELECT
			ORG_uuid
		FROM
			UAP.T_UAP_ORG j
		WHERE
			j.PARENT_ORG IS NULL START WITH j.ORG_UUID = g.ORG_ID CONNECT BY PRIOR j.PARENT_ORG = j.ORG_UUID
	) topAgentOrgNo,(
		SELECT
			org_nm
		FROM
			uap.t_uap_org
		WHERE
			org_uuid = tuo.BRANCH_ATTRIBUTION
	) BRANCH_NM,
	TO_CHAR (
		g.STORAGE_DATE,
		'YYYY-MM-DD'
	) AS STORAGE_DATE,
	TO_CHAR (gs.ACT_DATE, 'YYYY-MM-DD') AS ACT_DATE,
	TO_CHAR (gs.USE_DATE, 'YYYY-MM-DD') AS USE_DATE,
	sm.FACT_SN AS posFactSn,
	sm.POS_NUM,
	bm.mno,
	bm.CPR_REG_NM_CN
FROM
	OMS.T_gm_device_sim gs
LEFT JOIN OMS.T_gm_device g ON g.device_id = gs.device_id
LEFT JOIN uap.t_uap_org tuo ON g.ORG_ID = tuo.org_uuid
LEFT JOIN SMSA.T_SMSA_SIMPOS_MAPPING sm ON sm.IMSI = gs.IMSI
LEFT JOIN bap.T_bap_mec_if bm ON sm.in_mno = bm.in_mno
where g.goods_id='012'
<if test="branchNo!= null and branchNo != ''">
  and tuo.BRANCH_ATTRIBUTION=#{branchNo}
</if>
<if test="topAgentOrgNo!= null and topAgentOrgNo != ''">
  and (SELECT ORG_uuid FROM UAP.T_UAP_ORG j WHERE j.PARENT_ORG IS NULL START WITH j.ORG_UUID = g.ORG_ID CONNECT BY  PRIOR j.PARENT_ORG =  j.ORG_UUID )=#{topAgentOrgNo}
</if>
<if test="factSn!= null and factSn != ''">
  and (g.FACT_SN like '%'||#{factSn}||'%' or gs.IMSI like '%'||#{factSn}||'%')
</if>
<if test="storageDateStart != null and storageDateStart != '' ">   
 <![CDATA[
	   and g.STORAGE_DATE >= to_date(#{storageDateStart},'yyyy-mm-dd')   ]]>
</if>
<if test="storageDateEnd != null and storageDateEnd != '' ">  
 <![CDATA[
	   and g.STORAGE_DATE <= to_date(#{storageDateEnd}||'23:59:59','yyyy-mm-dd hh24:mi:ss')   ]]>
</if>
<if test="factId!= null and factId != ''">
  and g.fact_id=#{factId}
</if>
<if test="status!= null and status != '' and status != 0">
  and gs.STATUS=#{status}
</if>
<if test="actDateStart != null and actDateStart != '' ">   
 <![CDATA[
	   and gs.ACT_DATE >= to_date(#{actDateStart},'yyyy-mm-dd')   ]]>
</if>
<if test="actDateEnd != null and actDateEnd != '' ">  
 <![CDATA[
	   and gs.ACT_DATE <= to_date(#{actDateEnd}||'23:59:59','yyyy-mm-dd hh24:mi:ss')   ]]>
</if>
<if test="useDateStart != null and useDateStart != '' ">   
 <![CDATA[
	   and gs.USE_DATE >= to_date(#{useDateStart},'yyyy-mm-dd')   ]]>
</if>
<if test="useDateEnd != null and useDateEnd != '' ">  
 <![CDATA[
	   and gs.USE_DATE <= to_date(#{useDateEnd}||'23:59:59','yyyy-mm-dd hh24:mi:ss')   ]]>
</if>
<if test="payStatus!= null and payStatus != ''">
  and gs.PAY_STATUS=#{payStatus}
</if>
<if test="isBindMec!= null and isBindMec != '' and isBindMec == 10">
  and sm.in_mno is not null
</if>
<if test="isBindMec!= null and isBindMec != '' and isBindMec == 11">
  and sm.in_mno is null
</if>
order by gs.USE_DATE desc